package features.advance.leetcode.sql;

import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.ToString;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.stream.Collectors;

/**
 * @author LIN
 * @date 2022-12-07 11:19
 */
public class InitData {

    public static Connection getConnection() throws SQLException {
        try {
            ClassLoader.getSystemClassLoader().loadClass("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
;
        return connection;
    }

    public static void main(String[] args) throws IOException, SQLException {
//        Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int);
//Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);


        String jsonTableData = "";
        InputStream stream = ClassLoader.getSystemResourceAsStream("json/table_test01.json");
        byte[] data= new byte[1024*1024];
        int r = -1;
        StringBuilder buffer = new StringBuilder();
        try {
            while((r=stream.read(data))!=-1){
                String s = new String(data, 0, r);
                buffer.append(s);
            }
            jsonTableData = buffer.toString();
        } catch (IOException e) {
            e.printStackTrace();
        }

        ObjectMapper objectMapper = new ObjectMapper();
        Table table = objectMapper.readValue(jsonTableData,Table.class);
//        System.out.println(map);
        String[] product = table.getHeaders().getProduct();


        String[] salse = table.getHeaders().getSales();

        Rows rows = table.getRows();

        String[][] d = rows.getProduct();
        String[][] d1 = rows.getSales();

        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        StringBuilder sql = new StringBuilder();
        for (String[] v : d) {
            sql.append("insert into Product (").append(String.join(",", product)).append(" ) values (");
            sql.append(Arrays.stream(v).map(e->"'"+e+"'").collect(Collectors.joining(",")));
            sql.append(")");
            statement.addBatch(sql.toString());
            sql = new StringBuilder();
        }
        for (String[] v : d1) {
            sql.append("insert into Sales (").append(String.join(",", salse)).append(") values (");
            sql.append(Arrays.stream(v).map(e->"'"+e+"'").collect(Collectors.joining(",")));
            sql.append(")");
            statement.addBatch(sql.toString());
            sql = new StringBuilder();
        }

        int[] count = statement.executeBatch();
        System.out.println(count);

    }



}
@Data
@AllArgsConstructor
@ToString
class Table{
    private Headers headers;
    private Rows rows;

    public Table() {
    }
}

@Data
@AllArgsConstructor
@ToString
 class Headers{
    @JsonProperty(value = "Product")
    private String[] Product;
    @JsonProperty(value = "Sales")
    private String[] Sales;

    public Headers() {
    }
}

@Data
@AllArgsConstructor
@ToString
class Rows{
    @JsonProperty(value = "Product")
    private String[][] Product;
    @JsonProperty(value = "Sales")
    private String[][] Sales;

    public Rows() {
    }
}
